from dbpool import POOL
import pymysql

def create_conn(): # 创建连接
    conn = POOL.connection()
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return conn,cursor

def close_conn(conn,cursor): # 关闭连接
    cursor.close()
    conn.close()


def fetch_all(sql,args=None): # 查询全部
    conn,cursor = create_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res

# sql = "select * from users"
#
# print(fetch_all(sql)) # [{‘id‘: 1, ‘name‘: ‘小明‘, ‘age‘: 18}, {‘id‘: 2, ‘name‘: ‘小黑‘, ‘age‘: 17}, {‘id‘: 3, ‘name‘: ‘小白‘, ‘age‘: 16}]

def fetch_one(sql,args): # 单个查询
    conn,cursor = create_conn()
    cursor.execute(sql,args)
    res = cursor.fetchone()
    close_conn(conn,cursor)
    return res

# sql = "select * from users where name=%s and age=%s"
#
# print(fetch_one(sql,("小明",18))) # {‘id‘: 1, ‘name‘: ‘小明‘, ‘age‘: 18}

def insert(sql,args):
    conn,cursor = create_conn()
    res = cursor.execute(sql,args)
    conn.commit()
    close_conn(conn, cursor)
    return res

# sql = "insert into users(id,name,age) value (%s,%s,%s)"
#
# print(insert(sql,(4,"小王",20)))

def delete(sql,args):
    conn, cursor = create_conn()
    res = cursor.execute(sql, args)
    conn.commit()
    close_conn(conn, cursor)
    return res
